*Code to reproduce the continuous part of Table III “An ordinary TPM of the effect of local spending on OCV in 2010”, using the integrated “Dez_and_Death_integrated_dataset.dta”

keep if year == 2010
tabulate Estado_ID, gen(Estado)	

* Variables related to security spending per capita and the local police pear capita are multiply imputed with 50 datased (M=50)
mi set mlong
mi register imputed Sec_Spending_Cap_2010 Loc_Police_Per_Head
mi register regular CIDE_OC_deaths_Cap_Log Spending_Cap_Real Own_Revenue_Share gini poverty Share_Head_of_HH_Female Household_Size Population_Density  Population_15_29_Males  Males_Per_100_Females Indigenous_Pop_Share Population Munic_Size_Categ Laakso_Tag_Ind Dummy_Border_PCoast Estado1-Estado32
mi impute mvn Sec_Spending_Cap_2010 Loc_Police_Per_Head = CIDE_OC_deaths_Cap_Log Spending_Cap_Real Own_Revenue_Share gini poverty Share_Head_of_HH_Female Household_Size Population_Density  Population_15_29_Males  Males_Per_100_Females Indigenous_Pop_Share Population Munic_Size_Categ Laakso_Tag_Ind Dummy_Border_PCoast Estado1-Estado32, add(50) force
mi xeq 0 1 20 50: summarize Sec_Spending_Cap_2010 Loc_Police_Per_Head
mi estimate: regress CIDE_OC_deaths_Cap_Log Spending_Cap_Real Own_Revenue_Share Sec_Spending_Cap_2010 Loc_Police_Per_Head gini poverty Share_Head_of_HH_Female Household_Size Population_Density  Population_15_29_Males  Males_Per_100_Females Indigenous_Pop_Share Population Munic_Size_Categ Laakso_Tag_Ind Dummy_Border_PCoast i.Estado_ID, cluster(Estado_ID)

*Results and important additional parameters are stored in an excel file
putexcel set "resultsCS.xlsx", sheet("OLS_2010_MI_OC") modify
putexcel I3=(" Number of obs")
putexcel J3=( e(N_mi))
putexcel I4=("Number of groups")
putexcel J4=(e(N_clust))

putexcel I5=("F_mi")
putexcel J5=( e(F_mi))
matrix a = r(table)'
matrix a = a[., 1..6]
putexcel A2=matrix(a)

*combining pseudo R2 for all M
mi query
local M=50
scalar r2 = 0
 qui mi xeq 1/`M': regress CIDE_OC_deaths_Cap_Log Spending_Cap_Real Own_Revenue_Share Sec_Spending_Cap_2010 Loc_Police_Per_Head gini poverty Share_Head_of_HH_Female Household_Size Population_Density  Population_15_29_Males  Males_Per_100_Females Indigenous_Pop_Share Population Munic_Size_Categ Laakso_Tag_Ind Dummy_Border_PCoast i.Estado_ID, cluster(Estado_ID); scalar r2 = r2 + atanh(sqrt(e(r2)))
. scalar r2 = tanh(r2/`M')^2
. di as txt "R2 using Fisher's z over imputed data = " as res r2

* Storing results in an excel file
putexcel set "resultsCS.xlsx", sheet("OLS_2010_MI_OC") modify
putexcel I6=(" R2 using Fisher's z over imputed data ")
putexcel J6= (e(r2))
